package com.voicenews.recommendnews;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.voicenews.dao.ConnectionFactory;
import com.voicenews.model.Article;

public class RecommendNewsHandler {
	private Connection connection = null;
	private Statement statement = null;
	private ResultSet result = null;
	private String userId = null;

	public RecommendNewsHandler(String userId) {
		// TODO Auto-generated constructor stub
		this.userId = userId;
	}
	
	private Connection getConnection() throws SQLException {
		Connection conn;
		conn = ConnectionFactory.getInstance().getConnection();
		return conn;
	}

	public List<Article> getRecommendArticle() {
		// TODO Auto-generated method stub
		List<Article> articles = new ArrayList<Article>();
		String query = "select feed_id, count(feed_id) as 'amount' from article where id in (select article_id from user_article where user_id = "
				+ userId + ") group by feed_id order by amount desc";
		String queryArticles = "select * from article where feed_id = (?) order by pubdate desc limit 20";
		// String query = "select id from topic where name like 'xã hội'";
		System.out.println(query);

		String idFeed = null;
		try {
			connection = this.getConnection();
			statement = connection.prepareStatement(query);

			result = statement.executeQuery(query);
			while (result.next()) {
				idFeed = String.valueOf(result.getLong("feed_id"));
				break;
			}
			java.sql.PreparedStatement prepareStatement = connection
					.prepareStatement(queryArticles);
			prepareStatement.setString(1, idFeed);
			ResultSet articlesResultSet = prepareStatement.executeQuery();
			while (articlesResultSet.next()) {
				Article article = new Article();
				article.setArticleId(articlesResultSet.getLong("id"));
				article.setTitle(articlesResultSet.getString("title"));
				article.setDescription(articlesResultSet
						.getString("description"));
				article.setUrl(new java.net.URL(articlesResultSet
						.getString("url")));
				article.setImgLink(articlesResultSet.getString("image_link"));
				article.setPubDate(String.valueOf(articlesResultSet
						.getLong("pubdate")));
				article.setFeedId(articlesResultSet.getLong("feed_id"));
				String title = articlesResultSet.getString("title");
				String date = new Date(articlesResultSet.getLong("pubdate"))
						.toString();
				System.out.println(title + "\t" + date);
				articles.add(article);

			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}

			if (connection != null)
				try {
					connection.close();
				} catch (SQLException logOrIgnore) {
				}
		}

		return articles;
	}

}
